﻿using System.Linq;
using System.Collections.Generic;
using CML.Intercamber.Business.Model;

namespace CML.Intercamber.Business.Dao
{
    public class MailMessagesDao
    {
        public static List<MailMessagesDetail> ListMessagesToNotify()
        {
            List<MailMessagesDetail> res;
            using (var context = new IntercamberEntities())
            {
                res = (from tu in context.ThreadUsers
                       join t in context.Threads on tu.IdThread equals t.IdThread
                       join tm in context.ThreadMessages on t.IdThread equals tm.IdThread
                       where tm.DateMessage > tu.DateLastSeen && tm.IdUser != tu.IdUser
                       join tuu in context.ThreadUsers on tu.IdThread equals tuu.IdThread
                       where tuu.IdUser != tu.IdUser
                       join du in context.Users on tuu.IdUser equals du.IdUser
                       join de in context.Establishments on du.IdEstablishment equals de.IdEstablishment
                       join su in context.Users on tu.IdUser equals su.IdUser
                       join lu in context.Countries on su.IdCountry equals lu.IdCountry
                       select new
                       {
                           su.IdUser, 
                           su.Email, 
                           lu.IdLanguage, 
                           su.FirstName, 
                           DestName = du.FirstName + " " + du.LastName, 
                           DestEstablishmentName = de.LblEstablishment + "(" + de.IdCountry + ")",
                           MsgDate = tm.DateMessage
                       } into x
                       group x by new
                       {
                           x.IdUser,
                           x.Email,
                           x.IdLanguage, 
                           x.FirstName,
                           x.DestName,
                           x.DestEstablishmentName,
                       } into g
                       select new MailMessagesDetail
                       {
                           IdUser = g.Key.IdUser,
                           Email = g.Key.Email,
                           IdLanguage = g.Key.IdLanguage, 
                           FirstName = g.Key.FirstName,
                           DestName = g.Key.DestName,
                           DestEstablishmentName = g.Key.DestEstablishmentName,
                           DateLastMessage = g.Max(z => z.MsgDate),
                           UnreadMessagesCount = g.Count()
                       }).ToList();
            }
            return res;
        }
    }
}
